﻿CREATE PROCEDURE [branding].[ResolveBlob]
	@resourceId varchar(200),
	@tag varchar(50),
	@subTag varchar(50),
	@brand varchar(200)
AS
	declare @blob table
	(
		BlobId int,
		Brand varchar(200)
	)

	insert	@blob (BlobId, Brand)
	select	ID as BlobId,
			Brand
	from	branding.Blob
	where	ResourceId = @resourceId and
			(@tag is null or @tag = Tag) and
			(@subTag is null or @subTag = SubTag);

	select	top 1
			bb.ID,
			CAST('blob' as varchar(10)) as ObjectType,
			bb.Tag,
			bb.SubTag,
			bb.ResourceId,
			bb.Brand,
			bb.ContentLength,
			bb.MimeType,
			bb.Blob,
			bb.BlobGuid
	from	@blob as b
	inner   join branding.Blob as bb on bb.ID = b.BlobId
	where   CHARINDEX(b.Brand, @brand) != 0
	order   by LEN(b.Brand) desc
RETURN 0